﻿--use PolePosition
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_UtilityAssetMeters_SelectForGrid')
	DROP PROCEDURE up_UtilityAssetMeters_SelectForGrid
GO

CREATE PROCEDURE [dbo].[up_UtilityAssetMeters_SelectForGrid]
	 @UtilityFk int 
	,@PageSize int = 100
	,@PageNumber int = 1
	,@SearchByType varchar(50) = null
	,@SearchByText varchar(50) = null
AS

SET NOCOUNT ON	

DECLARE @StartRowNumber int
DECLARE @EndRowNumber int

SET @StartRowNumber = ((@PageNumber - 1) * @PageSize) + 1
SET @EndRowNumber = @PageNumber * @PageSize


DECLARE @table table
(
	 RowNumber int
	,TotalCount int
	,Utility_Asset_MeterId int
	,UtilityFK int
	,Latitude float
	,Longitude float
	,Altitude float
	,SatelliteCount int
	,SatelliteTime datetime
	,SolutionAvailable bit
	,Handheld nvarchar(100)
	,Meter_TopTypeFk int
	,Utility_ContactFK int
	,Date datetime
	,Utility_ProjectFK int
	,MapFk int
	,SectionFk int	
	,Number varchar(25)
	,[FriendlyName] varchar(255)
	,Comments ntext
	,NotesDescription varchar(max)
)
	
INSERT @table
(
	 [RowNumber]
	,[TotalCount]
	,[Utility_Asset_MeterId]
	,[UtilityFK]
	,[Latitude]
	,[Longitude]
	,[Altitude]
	,[SatelliteCount]
	,[SatelliteTime]
	,[SolutionAvailable]
	,[Handheld]
	,[Meter_TopTypeFk]
	,[Utility_ContactFK]
	,[Date]
	,[Utility_ProjectFK]
	,MapFk
	,SectionFk
	,Number
	,[FriendlyName]
	,Comments
	,NotesDescription
)
SELECT 
*
FROM 
(
	SELECT 
		 ROW_NUMBER() OVER (ORDER BY Utility_Asset_MeterId) AS RowNumber
		,COUNT(Utility_Asset_MeterId) OVER (PARTITION BY NULL) AS TotalCount
		,[Utility_Asset_MeterId]
		,[UtilityFK]
		,[Latitude]
		,[Longitude]
		,[Altitude]
		,[SatelliteCount]
		,[SatelliteTime]
		,[SolutionAvailable]
		,[Handheld]
		,[Meter_TopTypeFk]
		,[Utility_ContactFK]
		,[Date]
		,[Utility_ProjectFK]
		,MapFk	
		,SectionFk
		,Number
		,[FriendlyName]
		,Comments
		,NotesDescription
	from v_tb_Utility_Asset_Meters
	WHERE
		[UtilityFK] = @UtilityFk
) AS Data
WHERE RowNumber BETWEEN @StartRowNumber AND @EndRowNumber

-- primary select
SELECT * FROM @table








GO

